package com.yfeng.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yfeng.common.dao.IDao;
import com.yfeng.common.dao.Param;
import com.yfeng.common.utils.SqlUtil;

/**
 * 基本DAO实现
 */
public class BaseDaoImpl extends NamedParameterJdbcDaoSupport implements IDao {

	private Log log = LogFactory.getLog(BaseDaoImpl.class);

	/**
	 * 查询
	 * 
	 * @param strSql
	 *            一条SQL语句，如果只是from xx 那么将返回表中所有字段
	 * @param param
	 *            参数,一个Map Object
	 * @return 返回一个list对象，每行
	 */
	public List query(String strSql, Param param) throws Exception {
		log.info("======>" + strSql);
		try {
			if (strSql.startsWith("from")) {
				strSql = "select * " + strSql;
				return this.getNamedParameterJdbcTemplate().queryForList(
						strSql, param.toMap());
			} else
				return this.getNamedParameterJdbcTemplate().queryForList(
						strSql, param.toMap());
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 查询,不能有条件
	 * 
	 * @param strSql
	 *            SQL语句 如from xx
	 * @return 一个LIST对象
	 * @throws Exception
	 */
	public List query(String strSql) throws Exception {
		log.info("======>" + strSql);
		try {
			if (strSql.startsWith("from")) {
				strSql = "select * " + strSql;
				return this.getNamedParameterJdbcTemplate().queryForList(
						strSql, new HashMap());
			} else
				return this.getNamedParameterJdbcTemplate().queryForList(
						strSql, new HashMap());
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 取记录数 例如 from table where ...
	 * 
	 * @param strSql
	 *            sql语句，可以不用加select *
	 * @param param
	 *            map参数
	 * @return
	 */
	public int getRowCount(String strSql, Param param) throws Exception {
		try {
			if (strSql.startsWith("from")) {
				strSql = "select count(*) " + strSql;
				return this.getNamedParameterJdbcTemplate().queryForInt(strSql,
						param.toMap());
			} else
				return this.getNamedParameterJdbcTemplate().queryForInt(strSql,
						param.toMap());
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 取记录数 不能有参数
	 * 
	 * @param strSql
	 *            SQL语句� from users
	 * @return
	 */
	public int getRowCount(String strSql) throws Exception {
		try {
			if (strSql.startsWith("from")) {
				strSql = "select count(*) " + strSql;
				return this.getNamedParameterJdbcTemplate().queryForInt(strSql,
						new Param().toMap());
			} else
				return this.getNamedParameterJdbcTemplate().queryForInt(strSql,
						new Param().toMap());
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * update/insert 根据参数判断是Update还是Insert
	 * 
	 * @param strSql
	 * @param param
	 * @return update返回影响的行数,insert返回字段的主键
	 */
	public int save(String strSql, Param param) throws Exception {
		log.info("======>" + strSql);
		try {
			if (!param.isEmpty()) {

				String flag = param.getFirstKey();
			
				if (flag.startsWith("value__") || flag.startsWith("cond__"))
					// return
					// this.getNamedParameterJdbcTemplate().update(strSql,param.toMap());
					return this.updateByAuto(strSql, param);
				else
					return insertAndGetKeyByAuto(strSql, param); // 返回主键

			} else
				return -1; // error
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 写SQL语句来实现更新或新增
	 * 
	 * @param strSql
	 * @param param
	 * @return
	 */
	public int saveBySql(String strSql, Param param) throws Exception {
		try {
			return this.getNamedParameterJdbcTemplate().update(strSql,
					param.toMap());
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 新增记录方法,根据param自动拼装SQL
	 * 
	 * @param strTable
	 *            表名
	 * @param param
	 *            Map一个LinkedHashMap 对象的参数集
	 * @return
	 */
	private int insertAndGetKeyByAuto(String strTable, Param param)
			throws Exception {
		try {
			final String sql = SqlUtil.getInsertSql(strTable, param);// 根据map中的内容自动拼装SQL
			log.info("====>" + sql);
			final Param p = param;
			JdbcOperations jdbcTemplate = this.getNamedParameterJdbcTemplate()
					.getJdbcOperations();
			KeyHolder keyHolder = new GeneratedKeyHolder();
			jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn)
						throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql,
							new String[] { "id" });// return�id
					int i = 1;
					for (Object o : p.keySet()) {
						ps.setString(i, p.get(o).toString());
						i++;
					}
					return ps;
				}

			}, keyHolder);
			return keyHolder.getKey().intValue();
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 通过参数自动实现更新，不用写SQL语句
	 * 
	 * @param strTable
	 * @param param
	 * @return
	 */
	private int updateByAuto(String strTable, Param param) throws Exception {
		try {
			final String sql = SqlUtil.getUpdateSql(strTable, param);// 根据map中的内容自动拼装SQL
			log.info("############====>" + sql);
			final Param p = param;
			JdbcOperations jdbcTemplate = this.getNamedParameterJdbcTemplate()
					.getJdbcOperations();

			int ret = jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn)
						throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);// return�id
					int i = 1;

					for (Object o : p.keySet()) {
						ps.setString(i, p.get(o).toString());
						i++;
					}
					return ps;
				}

			});
			return ret;

		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 批处理 新增/修改
	 * 
	 * @param strSql
	 *            表名
	 * @param l
	 * @return
	 */
	public int[] batchSave(String strSql, List l) throws Exception {
		try {
			if (l.size() > 0) {
				final List paramsList = l;
				Param param = (Param) l.get(0);
				String tmpSql = "";
				if (!param.isEmpty()) {
					String flag = param.getFirstKey();
					if (flag.startsWith("value__") || flag.startsWith("cond__"))
						tmpSql = SqlUtil.getUpdateSql(strSql, param);// 根据map中的内容自动拼装SQL
					else
						tmpSql = SqlUtil.getInsertSql(strSql, param);// 根据map中的内容自动拼装SQL
				}
				final String sql = tmpSql;
				log.info("############====>" + sql);
				JdbcOperations jdbcTemplate = this
						.getNamedParameterJdbcTemplate().getJdbcOperations();
				BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
					public void setValues(PreparedStatement ps, int i)
							throws SQLException {
						int x = 1;
						Param p = (Param) paramsList.get(i);
						for (Object o : p.keySet()) {
							ps.setString(x, p.get(o).toString());
							x++;
						}
					}

					public int getBatchSize() {
						return paramsList.size();
					}
				};
				return jdbcTemplate.batchUpdate(sql, setter);
			} else
				return new int[] { -1 };

		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 删除操作
	 * 
	 * @param strTable
	 * @param params
	 * @return
	 */
	public int delete(String strTable, Param params) throws Exception {
		try {
			return deleteByAuto(strTable, params);
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 根据表和参数自动删除
	 * 
	 * @param strTable
	 * @param params
	 * @return
	 * @throws Exception
	 */
	private int deleteByAuto(String strTable, Param params) throws Exception {
		try {
			final String sql = SqlUtil.getDeleteSql(strTable, params);// 根据map中的内容自动拼装SQL
			final Param p = params;
			JdbcOperations jdbcTemplate = this.getNamedParameterJdbcTemplate()
					.getJdbcOperations();

			int ret = jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn)
						throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);// return�id
					int i = 1;
					for (Object o : p.keySet()) {
						ps.setString(i, p.get(o).toString());
						i++;
					}
					return ps;
				}

			});
			return ret;
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

	/**
	 * 自己写SQL语句来进行删除
	 * 
	 * @param strSql
	 * @param param
	 * @return
	 * @throws Exception
	 */
	public int deleteBySql(String strSql, Param param) throws Exception {
		try {
			return this.getNamedParameterJdbcTemplate().update(strSql,
					param.toMap());
		} catch (Exception e) {
			throw new Exception("DAO异常："+e.getMessage(), e);
		}
	}

}
